<%@ page language="java" contentType="text/json; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@page trimDirectiveWhitespaces="true"%>    
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@ page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>

<%

	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
			
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
	
	if("admin".equals(htp_session.getAttribute("sUser"))){%>
	<script>alert("This user dont have PERMISSION to access this zone.");</script>
	<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=admin_home.jsp"> 	
	<%}
	
	String sql_con = request.getParameter("sql_con");
	System.out.println("@ Background search enroll sql_con:"+sql_con);
	String Json = "";
	ObjectMapper mapper = new ObjectMapper();
	
	//set Database Connection
		String hostProps = "";
		String usernameProps  = "";
		String passwordProps  = "";
		String databaseProps = "";
		
		try {
			//get current path
			ServletContext servletContext = request.getSession().getServletContext();
			
			InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
			Properties props = new Properties();
			
			props.load(input);

			hostProps  = props.getProperty("host");
			usernameProps  = props.getProperty("username");
			passwordProps  = props.getProperty("password");
			databaseProps = props.getProperty("database");
		} catch (Exception e) { 
			out.println(e);  
		}
	
	// connect database
	Statement statement = null;
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620");//////"&characterEncoding=tis620" Encoding Thai);
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
			
			List<Student> student = new ArrayList<Student>();
			ResultSet rs = 
					connect.createStatement().executeQuery(" SELECT C.course_id, C.course_name_en, E.grade "+
														   " FROM student S, enrollment E, course C "+
														   " WHERE S.student_id = E.student_id AND E.course_id = C.course_id "+
														    sql_con);
			
			while(rs.next())
			{
				Student stu = new Student();
		
				stu.setPicture(rs.getString("picture"));
				stu.setStudentId(rs.getString("student_id"));
				stu.setYear(rs.getString("year"));
				stu.setNameTH(rs.getString("name_th"));
				stu.setSurnameTH(rs.getString("surname_th"));
				stu.setDepartment(rs.getString("department_name_th"));
				stu.setStatus(rs.getString("status_name"));
				
				
				student.add(stu);
			}		
			
			try {
				Json = mapper.writeValueAsString(student); 
			} catch (JsonGenerationException ex) {
				ex.printStackTrace();
			} catch (JsonMappingException ex) {
				ex.printStackTrace();
			} catch (IOException ex) {
				ex.printStackTrace();
			}
			
			out.print(Json);
			
			System.out.println("Size of student list :"+ student.size());
			
		
			System.out.println("Json :"+ Json);	
			
			
		} else {
			System.out.println("Database Connect Failed.");	

		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}

	connect.close();
%>